﻿Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports System.Globalization

'*// Bron: http://www.visualbasic.be/PNphpBB2-viewtopic-t-8233.phtml

Module mdlExcel

    Public Sub GridExportToExcel(ByVal cDG As ucMyGrid)

        Try
            'objPlsWait.ShowWaitScreen("Exporting to Excel..." & vbNewLine & "This may take a moment")

            Application.DoEvents()

            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
            Dim i, j As Integer
            Dim iNumber As Double, dDate As DateTime ', dDateOffset As DateTimeOffset
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets.Add()
            '*// Elke kolomheader in de datagridview overzetten naar excel
            For Each col As DataGridViewColumn In cDG.Columns
                xlWorkSheet.Cells(1, col.Index + 1) = col.HeaderText.ToString
            Next
            '*// Alle data overzetten...

            'frmMDIBOK.StatusBarInitialize("Export to MSExcel", 0, cDG.RowCount - 1)

            For i = 0 To cDG.RowCount - 1
                Application.DoEvents()
                If Not cDG.Rows(i).IsNewRow And cDG.Rows(i).Visible Then
                    For j = 0 To cDG.ColumnCount - 1
                        If cDG.Columns(j).Visible Then


                            Select Case cDG(j, i).Value.GetType.Name.ToString
                                Case "Double"
                                    '*// http://msdn.microsoft.com/en-us/library/0c899ak8.aspx
                                    iNumber = cDG(j, i).Value
                                    xlWorkSheet.Cells(i + 2, j + 1) = iNumber.ToString("#.##", CultureInfo.CurrentCulture)

                                Case "DateTime"
                                    '*// http://msdn.microsoft.com/en-us/library/az4se3k1.aspx
                                    dDate = cDG(j, i).Value
                                    xlWorkSheet.Cells(i + 2, j + 1) = dDate.ToString("u", CultureInfo.CurrentCulture)


                                Case Else
                                    xlWorkSheet.Cells(i + 2, j + 1) = cDG(j, i).Value.ToString.Replace(vbCrLf, ",")

                            End Select


                            'If cDG(j, i).Value.GetType.Name.ToString = "Double" Then


                            'Else

                            'End If

                        End If
                    Next
                End If

                'frmMDIBOK.StatusBarUpdate(i)
            Next
            xlWorkSheet.Range("A1", "W1").AutoFormat(Excel.XlRangeAutoFormat.xlRangeAutoFormat3DEffects1)
            xlApp.Visible = True
            releaseObject(xlWorkSheet)
            releaseObject(xlWorkBook)
            releaseObject(xlApp)

            mdlLOGGING.fLOG("Excel Export finished with " & cDG.RowCount - 1 & " rows", 50)


        Catch ex As COMException
            mdlLOGGING.fLOG("mdlExcel ==> GridExportToExcel: " & ex.Message.ToString, 99)

        Catch ex As Exception
            mdlLOGGING.fLOG("mdlExcel ==> GridExportToExcel: " & ex.Message.ToString, 99)

        Finally

            'frmMDIBOK.StatusBarTerminate()
        End Try

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        '*// release: open het gevormde excel-bestand
        Try

            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)

        Catch ex As Exception

            mdlLOGGING.fLOG("mdlExcel => ReleaseObject (Excel): " & ex.Message.ToString, 99)
        Finally
            obj = Nothing
            GC.Collect()
        End Try
    End Sub

End Module
